﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace HRM.DAL
{
    public class EngineDAL
    {
        private SqlConnection conn;
        private SqlCommand cmd;
        private SqlDataAdapter da;
        private DataSet ds = null;
        private string strSQL = "";
        private bool blnStatus = false;

        public EngineDAL()
        {
            conn = new SqlConnection(ConfigurationManager.AppSettings["HRM_DB"]);
        }

        private void OpenConnection()
        {
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
        }

        private void CloseConnection()
        {
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
                conn.Dispose();
                conn = null;
            }
        }

        protected internal DataSet GetRecord(string fieldSelect, string tableName)
        {
            try
            {
                strSQL = "Select " + fieldSelect + " From " + tableName;
                OpenConnection();
                cmd = new SqlCommand(strSQL, conn);
                da = new SqlDataAdapter(cmd);
                ds = new DataSet();
                da.Fill(ds, tableName);
                da.Dispose();
                cmd.Dispose();
            }
            catch
            {
                CloseConnection();
            }
            finally
            {
                CloseConnection();
            }

            return ds;
        }

        protected internal DataSet GetRecord(string fieldSelect, string tableName, string expression)
        {
            try
            {
                strSQL = "Select " + fieldSelect + " From " + tableName + " " + expression;
                OpenConnection();
                cmd = new SqlCommand(strSQL, conn);
                da = new SqlDataAdapter(cmd);
                ds = new DataSet();
                da.Fill(ds, tableName);
                da.Dispose();
                cmd.Dispose();
            }
            catch
            {
                CloseConnection();
            }
            finally
            {
                CloseConnection();
            }
            return ds;
        }

        protected internal DataSet GetRecord(string fieldSelect, string tableName, string fieldCompare, string valueCompare)
        {
            try
            {
                strSQL = "Select " + fieldSelect + " From " + tableName + " Where 1=1 ";
                strSQL += " And " + fieldCompare + "=@" + fieldCompare;
                OpenConnection();
                cmd = new SqlCommand(strSQL, conn);
                cmd.Parameters.AddWithValue("@" + fieldCompare, valueCompare);
                da = new SqlDataAdapter(cmd);
                ds = new DataSet();
                da.Fill(ds);
                da.Dispose();
                cmd.Dispose();
            }
            catch
            {
                CloseConnection();
            }
            finally
            {
                CloseConnection();
            }
            return ds;
        }

        protected internal DataSet GetRecord(string fieldSelect, string tableName, string fieldCompare, string valueCompare, string option)
        {
            try
            {
                strSQL = "Select " + fieldSelect + " From " + tableName + " Where 1=1 ";
                strSQL += " And " + fieldCompare + "=@" + fieldCompare + " " + option;
                OpenConnection();
                cmd = new SqlCommand(strSQL, conn);
                cmd.Parameters.AddWithValue("@" + fieldCompare, valueCompare);
                da = new SqlDataAdapter(cmd);
                ds = new DataSet();
                da.Fill(ds);
                da.Dispose();
                cmd.Dispose();
            }
            catch
            {
                CloseConnection();
            }
            finally
            {
                CloseConnection();
            }
            return ds;
        }

        protected internal DataSet GetRecord(string fieldSelect, string tableName, string[] fieldsCompare, string[] valuesCompare)
        {
            try
            {
                int len = fieldsCompare.Length < valuesCompare.Length ? fieldsCompare.Length : valuesCompare.Length;
                strSQL = "Select " + fieldSelect + " From " + tableName + " Where 1=1 ";
                OpenConnection();
                cmd = new SqlCommand();
                cmd.Connection = conn;
                if (len > 0)
                {
                    for (int i = 0; i < len; i++)
                    {
                        strSQL += " And " + fieldsCompare[i] + "=@" + fieldsCompare[i];
                        cmd.Parameters.AddWithValue("@" + fieldsCompare[i], valuesCompare[i]);
                    }
                }
                cmd.CommandText = strSQL;
                da = new SqlDataAdapter(cmd);
                ds = new DataSet();
                da.Fill(ds);
                da.Dispose();
                cmd.Dispose();
            }
            catch
            {
                CloseConnection();
            }
            finally
            {
                CloseConnection();
            }
            return ds;
        }

        protected internal DataSet GetRecord(string fieldSelect, string tableName, string[] fieldsCompare, string[] valuesCompare, string option)
        {
            try
            {
                int len = fieldsCompare.Length < valuesCompare.Length ? fieldsCompare.Length : valuesCompare.Length;
                strSQL = "Select " + fieldSelect + " From " + tableName + " Where 1=1 ";
                OpenConnection();
                cmd = new SqlCommand();
                cmd.Connection = conn;
                if (len > 0)
                {
                    for (int i = 0; i < len; i++)
                    {
                        strSQL += " And " + fieldsCompare[i] + "=@" + fieldsCompare[i];
                        cmd.Parameters.AddWithValue("@" + fieldsCompare[i], valuesCompare[i]);
                    }
                }
                strSQL += " " + option;
                cmd.CommandText = strSQL;
                da = new SqlDataAdapter(cmd);
                ds = new DataSet();
                da.Fill(ds);
                da.Dispose();
                cmd.Dispose();
            }
            catch
            {
                CloseConnection();
            }
            finally
            {
                CloseConnection();
            }
            return ds;
        }

        protected internal bool CheckRecordExist(string fieldSelect, string tableName)
        {
            blnStatus = false;
            try
            {
                strSQL = "Select " + fieldSelect + " From " + tableName;
                OpenConnection();
                cmd = new SqlCommand(strSQL, conn);
                SqlDataReader dr = cmd.ExecuteReader();
                if (dr.HasRows)
                {
                    dr.Close();
                    cmd.Dispose();
                    blnStatus = true;
                }
                dr.Close();
                cmd.Dispose();
            }
            catch
            {
                CloseConnection();
            }
            finally
            {
                CloseConnection();
            }
            return blnStatus;
        }

        protected internal bool CheckRecordExist(string fieldSelect, string tableName, string fieldCompare, string valueCompare)
        {
            blnStatus = false;
            try
            {
                strSQL = "Select " + fieldSelect + " From " + tableName + " Where 1=1 ";
                OpenConnection();
                cmd = new SqlCommand();
                cmd.Connection = conn;
                strSQL += " And " + fieldCompare + "=@" + fieldCompare;
                cmd.Parameters.AddWithValue("@" + fieldCompare, valueCompare);
                cmd.CommandText = strSQL;
                SqlDataReader dr = cmd.ExecuteReader();
                if (dr.HasRows)
                {
                    dr.Close();
                    cmd.Dispose();
                    blnStatus = true;
                }
                dr.Close();
                cmd.Dispose();
            }
            catch
            {
                CloseConnection();
            }
            finally
            {
                CloseConnection();
            }
            return blnStatus;
        }

        protected internal bool CheckRecordExist(string tableName, string[] fieldsCompare, string[] valuesCompare)
        {
            blnStatus = false;
            try
            {
                int len = (fieldsCompare.Length < valuesCompare.Length) ? fieldsCompare.Length : valuesCompare.Length;
                if (len > 0)
                {
                    strSQL = "Select * From " + tableName + " Where 1=1 ";
                    OpenConnection();
                    cmd = new SqlCommand();
                    cmd.Connection = conn;
                    for (int i = 0; i < len; i++)
                    {
                        strSQL += " And " + fieldsCompare[i] + "=@" + fieldsCompare[i];
                        cmd.Parameters.AddWithValue("@" + fieldsCompare[i], valuesCompare[i]);
                    }
                    cmd.CommandText = strSQL;
                    SqlDataReader dr = cmd.ExecuteReader();
                    if (dr.HasRows)
                    {
                        dr.Close();
                        cmd.Dispose();
                        blnStatus = true;
                    }
                    dr.Close();
                    cmd.Dispose();
                }
            }
            catch
            {
                CloseConnection();
            }
            finally
            {
                CloseConnection();
            }
            return blnStatus;
        }

        protected internal bool InsertRecord(string tableName, string[] fieldsName, string[] fieldsValues)
        {
            blnStatus = false;
            try
            {
                int len = (fieldsName.Length < fieldsValues.Length) ? fieldsName.Length : fieldsValues.Length;
                if (len > 0)
                {
                    strSQL = "Insert Into " + tableName + "(";
                    string tmpField = "", tmpValue = "";
                    OpenConnection();
                    cmd = new SqlCommand();
                    cmd.Connection = conn;
                    for (int i = 0; i < len; i++)
                    {
                        tmpField += fieldsName[i] + ",";
                        tmpValue += "@" + fieldsName[i] + ",";
                        cmd.Parameters.AddWithValue("@" + fieldsName[i], fieldsValues[i]);
                    }
                    strSQL = strSQL + tmpField.Substring(0, tmpField.Length - 1) + ") Values(" + tmpValue.Substring(0, tmpValue.Length - 1) + ")";
                    cmd.CommandText = strSQL;
                    if (cmd.ExecuteNonQuery() == 1)
                    {
                        blnStatus = true;
                    }
                    cmd.Dispose();
                }
            }
            catch
            {
                CloseConnection();
            }
            finally
            {
                CloseConnection();
            }
            return blnStatus;
        }

        protected internal bool UpdateRecord(string tableName, string fieldsName, string fieldsValue, string fieldCompare, string valueCompare)
        {
            blnStatus = false;
            try
            {
                if (!string.IsNullOrEmpty(fieldsName))
                {
                    strSQL = "Update " + tableName + " Set ";
                    OpenConnection();
                    cmd = new SqlCommand();
                    cmd.Connection = conn;
                    strSQL += fieldsName + "=@" + fieldsName;
                    cmd.Parameters.AddWithValue("@" + fieldsName, fieldsValue);
                    strSQL += " Where 1=1 ";
                    if (!string.IsNullOrEmpty(fieldCompare))
                    {
                        strSQL += " And " + fieldCompare + "=@" + fieldCompare;
                        cmd.Parameters.AddWithValue("@" + fieldCompare, valueCompare);
                    }
                    cmd.CommandText = strSQL;
                    if (cmd.ExecuteNonQuery() == 1)
                    {
                        blnStatus = true;
                    }
                    cmd.Dispose();
                }
            }
            catch
            {
                CloseConnection();
            }
            finally
            {
                CloseConnection();
            }
            return blnStatus;
        }

        protected internal bool UpdateRecord(string tableName, string[] fieldsName, string[] fieldsValues, string fieldCompare, string valueCompare)
        {
            blnStatus = false;
            try
            {
                int len1 = (fieldsName.Length < fieldsValues.Length) ? fieldsName.Length : fieldsValues.Length;

                if (len1 > 0)
                {
                    strSQL = "Update " + tableName + " Set ";
                    OpenConnection();
                    cmd = new SqlCommand();
                    cmd.Connection = conn;
                    for (int i = 0; i < len1; i++)
                    {
                        strSQL += fieldsName[i] + "=@" + fieldsName[i] + ",";
                        cmd.Parameters.AddWithValue("@" + fieldsName[i], fieldsValues[i]);
                    }
                    strSQL = strSQL.Substring(0, strSQL.Length - 1);
                    strSQL += " Where 1=1 ";
                    if (!string.IsNullOrEmpty(fieldCompare))
                    {
                        strSQL += " And " + fieldCompare + "=@" + fieldCompare;
                        cmd.Parameters.AddWithValue("@" + fieldCompare, valueCompare);
                    }
                    cmd.CommandText = strSQL;
                    if (cmd.ExecuteNonQuery() == 1)
                    {
                        blnStatus = true;
                    }
                    cmd.Dispose();
                }
            }
            catch
            {
                CloseConnection();
            }
            finally
            {
                CloseConnection();
            }
            return blnStatus;
        }

        protected internal bool UpdateRecord(string tableName, string[] fieldsName, string[] fieldsValues, string[] fieldsCompare, string[] valuesCompare)
        {
            try
            {
                int len1 = (fieldsName.Length < fieldsValues.Length) ? fieldsName.Length : fieldsValues.Length;
                int len2 = (fieldsCompare.Length < valuesCompare.Length) ? fieldsCompare.Length : valuesCompare.Length;

                if (len1 > 0 && len2 > 0)
                {
                    strSQL = "Update " + tableName + " Set ";
                    OpenConnection();
                    cmd = new SqlCommand();
                    cmd.Connection = conn;
                    for (int i = 0; i < len1; i++)
                    {
                        strSQL += fieldsName[i] + "=@" + fieldsName[i] + ",";
                        cmd.Parameters.AddWithValue("@" + fieldsName[i], fieldsValues[i]);
                    }
                    strSQL = strSQL.Substring(0, strSQL.Length - 1);
                    strSQL += " Where 1=1 ";
                    for (int i = 0; i < len2; i++)
                    {
                        strSQL += " And " + fieldsCompare[i] + "=@" + fieldsCompare[i];
                        cmd.Parameters.AddWithValue("@" + fieldsCompare[i], valuesCompare[i]);
                    }
                    cmd.CommandText = strSQL;
                    if (cmd.ExecuteNonQuery() == 1)
                    {
                        blnStatus = true;
                    }
                    cmd.Dispose();
                }
            }
            catch
            {
                CloseConnection();
            }
            finally
            {
                CloseConnection();
            }
            return blnStatus;
        }

        protected internal bool DeleteRecord(string tableName, string fieldCompare, string valueCompare)
        {
            blnStatus = false;
            try
            {
                if (!string.IsNullOrEmpty(fieldCompare) && !string.IsNullOrEmpty(valueCompare))
                {
                    strSQL = "Delete From " + tableName + " Where " + fieldCompare + "=@Parameter";
                    OpenConnection();
                    cmd = new SqlCommand(strSQL, conn);
                    cmd.Parameters.AddWithValue("@Parameter", valueCompare);
                    if (cmd.ExecuteNonQuery() == 1)
                    {
                        blnStatus = true;
                    }
                    cmd.Dispose();
                }
            }
            catch
            {
                CloseConnection();
            }
            finally
            {
                CloseConnection();
            }
            return blnStatus;
        }

        protected internal bool DeleteRecord(string tableName, string expression)
        {
            blnStatus = false;
            try
            {
                if (!string.IsNullOrEmpty(expression))
                {
                    strSQL = "Delete From " + tableName + " Where " + expression;
                    OpenConnection();
                    cmd = new SqlCommand(strSQL, conn);
                    if (cmd.ExecuteNonQuery() == 1)
                    {
                        blnStatus = true;
                    }
                    cmd.Dispose();
                }
            }
            catch
            {
                CloseConnection();
            }
            finally
            {
                CloseConnection();
            }
            return blnStatus;
        }

        protected internal SqlCommand GetCmd(string fieldSelect, string tableName)
        {
            strSQL = "Select " + fieldSelect + " From " + tableName;
            OpenConnection();
            cmd = new SqlCommand(strSQL, conn);
            return cmd;
        }

        protected internal SqlCommand GetCmd(string fieldSelect, string tableName, string expression)
        {
            strSQL = "Select " + fieldSelect + " From " + tableName + " " + expression;
            OpenConnection();
            cmd = new SqlCommand(strSQL, conn);
            return cmd;
        }

        protected internal SqlCommand GetCmd(string fieldSelect, string tableName, string fieldCompare, string valueCompare)
        {
            strSQL = "Select " + fieldSelect + " From " + tableName + " Where 1=1 ";
            OpenConnection();
            cmd = new SqlCommand();
            cmd.Connection = conn;
            if (!string.IsNullOrEmpty(fieldCompare))
            {
                strSQL += " And " + fieldCompare + "=@Parameter";
                cmd.Parameters.AddWithValue("@Parameter", valueCompare);
            }
            cmd.CommandText = strSQL;
            return cmd;
        }

        protected internal SqlCommand GetCmd(string fieldSelect, string tableName, string[] fieldsCompare, string[] valuesCompare)
        {
            strSQL = "Select " + fieldSelect + " From " + tableName + " Where 1=1 ";
            int len = fieldsCompare.Length < valuesCompare.Length ? fieldsCompare.Length : valuesCompare.Length;
            OpenConnection();
            cmd = new SqlCommand();
            cmd.Connection = conn;
            if (len > 0)
            {
                for (int i = 0; i < len; i++)
                {
                    strSQL += " And " + fieldsCompare[i].ToString() + "=@Parameter" + i.ToString();
                    cmd.Parameters.AddWithValue("@Parameter" + i.ToString(), valuesCompare[i]);
                }
            }
            cmd.CommandText = strSQL;
            return cmd;
        }

        protected internal DataTable GetDataTable(string fieldSelect, string tableName)
        {
            DataTable dataTable = new DataTable();
            try
            {
                strSQL = "Select " + fieldSelect + " From " + tableName;
                OpenConnection();
                da = new SqlDataAdapter(strSQL, conn);
                da.Fill(dataTable);
                da.Dispose();
            }
            catch
            {
                CloseConnection();
            }
            finally
            {
                CloseConnection();
            }

            return dataTable;
        }

        protected internal void UpdateAdapter(string fieldSelect, string tableName, DataTable dataTable)
        {
            try
            {
                strSQL = "Select " + fieldSelect + " From " + tableName;
                OpenConnection();
                da = new SqlDataAdapter(strSQL, conn);
                SqlCommandBuilder commandBuilder = new SqlCommandBuilder(da);
                da.Update(dataTable);
                da.Dispose();
            }
            catch
            {
                CloseConnection();
            }
            finally
            {
                CloseConnection();
            }
        }

        protected internal int GetTotalRecord(string tableName)
        {
            int total = 0;
            try
            {
                strSQL = "Select Count(*) From " + tableName;
                OpenConnection();
                cmd = new SqlCommand(strSQL, conn);
                total = Convert.ToInt32(cmd.ExecuteScalar());
                cmd.Dispose();
            }
            catch
            {
                CloseConnection();
            }
            finally
            {
                CloseConnection();
            }
            return total;
        }

        protected internal int GetTotalRecord(string tableName, string expression)
        {
            int total = 0;
            try
            {
                strSQL = "Select Count(*) From " + tableName + " " + expression;
                OpenConnection();
                cmd = new SqlCommand(strSQL, conn);
                total = Convert.ToInt32(cmd.ExecuteScalar());
                cmd.Dispose();
            }
            catch
            {
                CloseConnection();
            }
            finally
            {
                CloseConnection();
            }
            return total;
        }

        protected internal int GetMaxID(string field, string tableName)
        {
            int maxID = 0;
            try
            {
                strSQL = "Select MAX(" + field + ") From " + tableName;
                OpenConnection();
                cmd = new SqlCommand(strSQL, conn);
                maxID = Convert.ToInt32(cmd.ExecuteScalar());
                cmd.Dispose();
            }
            catch
            {
                CloseConnection();
            }
            finally
            {
                CloseConnection();
            }
            return maxID;
        }

        protected internal bool ExecuteStored(string StoredName, SqlParameter[] ParmeterArray)
        {
            bool tmp = false;
            try
            {
                OpenConnection();
                SqlCommand sqlCommand = new SqlCommand(StoredName, this.conn);
                sqlCommand.CommandType = CommandType.StoredProcedure;
                sqlCommand.Parameters.AddRange(ParmeterArray);
                sqlCommand.ExecuteNonQuery();
                tmp = true;
                sqlCommand.Dispose();
            }
            catch
            {
                tmp = false;
                CloseConnection();
            }
            finally
            {
                CloseConnection();
            }
            return tmp;
        }
        
    }
}
